﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data;

using AnimeDLManager.Lib.Vars;

namespace AnimeDLManager.Lib
{
	class Db
	{
		private const string ConnString = @"server=www.freesql.org;
										 port=3306;
										 database=anidlman;
										 user id=" + Sec.DbUser + "; password=" + Sec.DbPass + ";";

		static MySqlConnection _myConn;

		/// <summary>
		/// Gathers information from the index table.
		/// </summary>
		/// <returns>The information gathered from the index table</returns>
		public static AniIndex[] GetIndex()
		{
			var aRes = new List<AniIndex>();
			var temp = new AniIndex();
			var lItems = new List<object>();

			try
			{
				var myDS = GetDA("aniIndex");

				lItems.AddRange(from DataTable myTable in myDS.Tables from DataRow myRenglon in myTable.Rows from DataColumn myColumn in myTable.Columns select myRenglon[myColumn].ToString());

				var aItems = lItems.ToArray();

				for (var i = 0; i < aItems.Length; i += AniIndex.Total)
				{
					temp.Id = int.Parse((string) aItems[i]);
					temp.AniName = (string) aItems[i + 1];
					temp.DbName = (string) aItems[i + 2];
					temp.IdAniDb = int.Parse((string) aItems[i + 3]);

					aRes.Add(new AniIndex { AniName = (string)aItems[i + 1],
											DbName = (string)aItems[i + 2],
											Id = int.Parse((string) aItems[i]),
											IdAniDb = int.Parse((string)aItems[i + 3])});
				}
			}
			catch
			{
				return null;
			}

			return aRes.ToArray();
		}

		/// <summary>
		/// Gets information from a table and displays them in a test windows.
		/// </summary>
		/// <param name="sTable">The name of the table to gather information from.</param>
		public static void TestTable(string sTable) {
			var sItems = new List<string>();
			var oData = new ListViewItem[4];
			var sData = new string[4];
			int i = 0;

			#region Form

			var form = new Form();
			var lstView = new ListView();

			form.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
			form.AutoScaleMode = AutoScaleMode.Font;
			form.ClientSize = new System.Drawing.Size(285, 270);
			form.Controls.Add(lstView);
			form.Name = "Form_TableTest";
			form.StartPosition = FormStartPosition.CenterParent;
			form.Text = @"Table test";
			form.ResumeLayout(false);

			lstView.Anchor = ((AnchorStyles.Top | AnchorStyles.Bottom)
			                  | AnchorStyles.Left)
			                 | AnchorStyles.Right;
			lstView.FullRowSelect = true;
			lstView.Location = new System.Drawing.Point(0, 0);
			lstView.Size = new System.Drawing.Size(285, 270);
			lstView.TabIndex = 0;
			lstView.UseCompatibleStateImageBehavior = false;
			lstView.View = View.Details;

			form.Show();

			#endregion Form

			try
			{
				var myDS = GetDA(sTable);

				foreach (DataColumn column in myDS.Tables["dsTable"].Columns)
					lstView.Columns.Add(new ColumnHeader().Text = column.Caption);

				sItems.AddRange(from DataTable myTable in myDS.Tables from DataRow myRenglon in myTable.Rows from DataColumn myColumn in myTable.Columns select myRenglon[myColumn].ToString());

				foreach (string sItem in sItems)
				{
					oData[i] = new ListViewItem(sItem);
					sData[i] = sItem;

					if ((i + 1) % lstView.Columns.Count == 0)
					{
						for (int i2 = 1; i2 < lstView.Columns.Count; i2++)
							oData[0].SubItems.Add(sData[i2]);

						lstView.Items.Add(oData[0]);
						i = 0;
					}
					else
					{
						i += 1;
					}
				}
			}
			catch
			{
				return;
			}
			finally
			{
				if (_myConn != null) _myConn.Close();
			}
		}

		private static DataSet GetDA(string sTable)
		{
			var myDS = new DataSet();
			try
			{
				_myConn = new MySqlConnection(ConnString);
				if (_myConn == null) _myConn.Open();

				var myCmd = new MySqlCommand("select * from " + sTable, _myConn);

				var myDA = new MySqlDataAdapter(myCmd);
				myDA.Fill(myDS, "dsTable");
			}
			catch (Exception)
			{
				throw new NullReferenceException();
			}
			finally
			{
				if (_myConn != null) _myConn.Close();
			}

			return myDS;
		}
	}
}
